import Levenshtein
import random
import os
import sqlite3 as db
import pymssql
import xlsxwriter
import openpyxl
import re
#词性还原
import nltk
from nltk.corpus.reader import NOUN
from nltk.stem import WordNetLemmatizer
from openpyxl.styles import Alignment, Side, Border, PatternFill
from openpyxl.styles import Font
from nltk.corpus import wordnet
from stanfordcorenlp import StanfordCoreNLP

from WordBean import WordBean

from nltk.corpus import wordnet
from nltk.corpus import stopwords
from nltk.corpus import brown
import numpy as np

db_path = "/Users/xuyan/Desktop/WH_Lexicon.db"
excelFile = "/Users/xuyan/PythonProject/CreateQuestion/question.xlsx"

'''
    返回nlp语料库中单词的完整句子
'''
def nlpCreateSentence(word):
    syn = wordnet.synsets(word)
    result = []
    for child in syn:
        examples = child.examples()
        for exampleChild in examples:
            example = str(exampleChild)
            words = example.split(" ")
            #已句子结束标点符号为结尾的句子并且包含单词的句子
            if symbol_endwiths(example) and word in example and (not example in "...") and len(words) > 8:
                result.append(example)
    #按字符串长度进行排序
    result = sorted(result, key=lambda i: len(i), reverse=True)
    return result


'''
    判断是否是有效的英文单词
    无效
'''
def checkEnglishWords(word):
    if not wordnet.synsets(word):
        return False
    return True

# 判断字符串是否以字母结尾
def string_endwiths(str):
    text = re.compile(r".*[a-zA-Z]$")
    if text.match(str):
        return True
    else:
        return False

'''
    判断字符串是否以'.'、'!'、'?'为结尾
'''
def symbol_endwiths(str):
    text = re.compile(r".*[.!?]$")
    if text.match(str):
        return True
    else:
        return False

'''
修正不正确的单词
1、单词中（s）、(u)等
2、
'''
def reviserWord(word:str):
    word = re.sub(u"(\\(.*?\\))|(（.*?）)","",word)
    return word

'''
去除'\t\b\\0\r\n'
'''
def trimStr(value:str):
    return re.sub('[^A-Za-z0-9-_()]+', '', value)

'''
    判断是否包含'.-_'
'''
def isIllegalWord(word:str):
    flag = ".-_"
    for i in flag:
        if i in word:
           return True
    return False

'''
    判断是否包含非法字符
'''
def isIllegalStr(value:str):
    search = re.search(r'\W',value)
    if search == None:
        return False
    return True

def listIndex(list,val):
    index = -1
    try:
        index = list.index(val)
    except:
        pass
    if index >= 0:
        return True
    return False

def toListIndex(list,val):
    index = -1
    try:
        index = list.index(val)
    except:
        pass
    return index

def firstWordUpper(word:str):
    return word.capitalize()

def listRemove(list,index):
    try:
        list.remove(list[index])
    except:
        pass

def numeric_compare(x, y):
    return x - y

def array_compare(x, y):
    return x[1] - y[1]

def arraySecond(elem):
    return elem[1]

'''
    从arr中找出与target中单词相似的单词
    @:param arr 单词词库
    @:param target 目标单词
    @:param threshold 最低分值
    @:param resultLength 最多返回单词的数量
'''
def Levenshtein_ratio(arr,target,threshold = 0.5,resultLength = 5):
    source = str(arr).strip().split(" ")
    result = []
    try:
        for child in source:
            ratio = Levenshtein.ratio(target,child)
            if ratio >= threshold and ratio < 1.0:
                childResult = [child,ratio]
                result.append(childResult)
        result.sort(key=arraySecond)
        result = result[-resultLength:]
    except:
        pass
    return result

'''
    先指定答案的选项位置
    A、B、C =>索引： 0,1,2 值：0-正确答案，1，2：干扰选项
'''
def pickOptions(optionCount = 3):
    options = []
    while len(options) < optionCount:
        rand = random.randint(0, optionCount-1)
        if not listIndex(options,rand):
            options.append(rand)
    return options


def copyOptions(options,right,answers):
    result = []
    for i in range(0, len(options)):
        opt = options[i]
        if opt == 0:
            result.append(right)
        result.append(answers[-opt])
    return result

'''
    返回单词原型
word: disgusting 	 wordID: 2040 	 subject: 令人作呕的,厌恶的 	 rightAnswer: B 	 ItemA: disgust 	 ItemB: disgusting 	 ItemC: missing
word: dish 	 wordID: 2042 	 subject: 盛…于盘中 	 rightAnswer: C 	 ItemA: wish 	 ItemB: disc 	 ItemC: dish    
'''
def word_protoType(word,pron=NOUN):
    # nltk.download('wordnet')
    wnl = WordNetLemmatizer()
    result=None
    try:
        result = wnl.lemmatize(word, pos=pron)
    except:
        pass
    if result == None or result == '':
        return word
    return result

wh_db_path = ""

'''
从数据库中查出相同拼写的单词
'''
def selectSQLWord(word,connSQL=1):
    global db_path
    wordList = []
    conn = None
    cursor = None
    if connSQL == 1:
        conn = connsqlite()
        cursor = conn.cursor()
        cursor.execute(
            "SELECT ID,Word,Chinese,Attachment,Part_Of_Speech,Word_Type,E_Phonetic_Symbol,U_Phonetic_Symbol FROM Wh_Word WHERE Word=?",
            (word,))
    else:
        conn = connsqlServer()
        cursor = conn.cursor()
        cursor.execute(
            "SELECT ID,Word,Chinese,Attachment,Part_Of_Speech,Word_Type,E_Phonetic_Symbol,U_Phonetic_Symbol FROM Wh_Word WHERE Word=%s",word)
    for child in cursor:
        word = WordBean(child[0], child[1], child[2], child[3], child[4], child[5], child[6], child[7])
        wordList.append(word)
    cursor.close()
    conn.close()
    return wordList


'''
从数据库中查出相同拼写的单词
'''
def selectSQLWord1(id,connSQL=1):
    global db_path
    wordList = []
    conn = None
    cursor = None
    if connSQL == 1:
        conn = connsqlite()
        cursor = conn.cursor()
        cursor.execute(
            "SELECT ID,Word,Chinese,Attachment,Part_Of_Speech,Word_Type,E_Phonetic_Symbol,U_Phonetic_Symbol FROM Wh_Word WHERE ID=?",
            (id,))
    else:
        conn = connsqlServer()
        cursor = conn.cursor()
        cursor.execute(
            "SELECT ID,Word,Chinese,Attachment,Part_Of_Speech,Word_Type,E_Phonetic_Symbol,U_Phonetic_Symbol FROM Wh_Word WHERE ID=%d",id)
    for child in cursor:
        word = WordBean(child[0], child[1], child[2], child[3], child[4], child[5], child[6], child[7],"","")
        wordList.append(word)
    cursor.close()
    conn.close()
    return wordList

def writeQuestionsToXls1(questionList,workSheetName):
    workbook = None
    if not os.path.exists(excelFile):
        workbook = openpyxl.Workbook()
    else:
        workbook = openpyxl.load_workbook(excelFile)
    #创建workSheet
    workSheet = workbook.create_sheet()
    workSheet.title = workSheetName
    #设置表头
    workSheet.cell(1, 1, 'WordID')
    workSheet.cell(1, 2, 'Word')
    workSheet.cell(1, 3, 'QuestionType')
    workSheet.cell(1, 4, 'Subject')
    workSheet.cell(1, 5, 'RightAnswer')
    workSheet.cell(1, 6, 'ItemA')
    workSheet.cell(1, 7, 'ItemB')
    workSheet.cell(1, 8, 'ItemC')
    workSheet.cell(1, 9, 'ItemD')
    # workSheet.cell(1, 10, 'WordDetail')

    border_type = Side(border_style=None, color='FF000000')
    border = Border(left=border_type,
                    right=border_type,
                    top=border_type,
                    bottom=border_type,
                    diagonal=border_type,
                    diagonal_direction=0,
                    outline=border_type,
                    vertical=border_type,
                    horizontal=border_type
                    )
    align = Alignment(horizontal='center', vertical='center', wrap_text=True)  # 纯色填充

    tableFont = Font(name='Calibri',
                size=14,
                color='666666',
                bold=True,
                italic=False,
                vertAlign=None,
                underline='none',
                strike=False)

    font = Font(name='Calibri',
                size=14,
                color='333333',
                bold=False,
                italic=False,
                vertAlign=None,
                underline='none',
                strike=False)


    # workSheet.rows[0].font = tableFont
    #     # workSheet.rows[0].border = border

    fill = PatternFill('solid', fgColor='FFBB00')  # 设置填充颜色为 橙色

    workSheet.cell(1,1).font = tableFont
    workSheet.cell(1,1).fill = fill
    workSheet.cell(1,1).alignment = align
    workSheet.cell(1,1).border = border_type
    workSheet.cell(1, 2).font = tableFont
    workSheet.cell(1, 2).fill = fill
    workSheet.cell(1, 2).alignment = align
    workSheet.cell(1, 2).border = border_type
    workSheet.cell(1, 3).font = tableFont
    workSheet.cell(1, 3).fill = fill
    workSheet.cell(1, 3).alignment = align
    workSheet.cell(1, 3).border = border_type
    workSheet.cell(1, 4).font = tableFont
    workSheet.cell(1, 4).fill = fill
    workSheet.cell(1, 4).alignment = align
    workSheet.cell(1, 4).border = border_type
    workSheet.cell(1, 5).font = tableFont
    workSheet.cell(1, 5).fill = fill
    workSheet.cell(1, 5).alignment = align
    workSheet.cell(1, 5).border = border_type
    workSheet.cell(1, 6).font = tableFont
    workSheet.cell(1, 6).fill = fill
    workSheet.cell(1, 6).alignment = align
    workSheet.cell(1, 6).border = border_type
    workSheet.cell(1, 7).font = tableFont
    workSheet.cell(1, 7).fill = fill
    workSheet.cell(1, 7).alignment = align
    workSheet.cell(1, 7).border = border_type
    workSheet.cell(1, 8).font = tableFont
    workSheet.cell(1, 8).fill = fill
    workSheet.cell(1, 8).alignment = align
    workSheet.cell(1, 8).border = border_type
    workSheet.cell(1, 9).font = tableFont
    workSheet.cell(1, 9).fill = fill
    workSheet.cell(1, 9).alignment = align
    workSheet.cell(1, 9).border = border_type
    # workSheet.cell(1, 10).font = tableFont
    # workSheet.cell(1, 10).fill = fill
    # workSheet.cell(1, 10).alignment = align
    # workSheet.cell(1, 10).border = border_type
    # workSheet['A:I'].alignment = align
    # workSheet['A:I'].font = font
    # workSheet['A:I'].fill = fill

    workSheet.column_dimensions['A'].width = 20
    workSheet.column_dimensions['B'].width = 20
    workSheet.column_dimensions['C'].width = 20
    workSheet.column_dimensions['D'].width = 20
    workSheet.column_dimensions['E'].width = 20
    workSheet.column_dimensions['F'].width = 20
    workSheet.column_dimensions['G'].width = 20
    workSheet.column_dimensions['H'].width = 20
    workSheet.column_dimensions['I'].width = 20
    # workSheet.column_dimensions['J'].width = 20

    #插入题目数据
    row = 2
    col = 1
    for question in questionList:
        cell = workSheet.cell(row, col, question.getWordID())
        cell.font = font
        cell.alignment = align
        cell = workSheet.cell(row, col + 1, question.getWord())
        cell.font = font
        cell.alignment = align
        cell = workSheet.cell(row, col + 2, question.getQuestionType())
        cell.font = font
        cell.alignment = align
        cell = workSheet.cell(row, col + 3, question.getSubject())
        cell.font = font
        cell.alignment = align
        cell = workSheet.cell(row, col + 4, question.getRightAnswer())
        cell.font = font
        cell.alignment = align
        cell = workSheet.cell(row, col + 5, question.getItemA())
        cell.font = font
        cell.alignment = align
        cell = workSheet.cell(row, col + 6, question.getItemB())
        cell.font = font
        cell.alignment = align
        cell = workSheet.cell(row, col + 7, question.getItemC())
        cell.font = font
        cell.alignment = align
        cell = workSheet.cell(row, col + 8, question.getItemD())
        cell.font = font
        cell.alignment = align
        # cell = workSheet.cell(row, col + 9, question.getWordDetail())
        # cell.font = font
        # cell.alignment = align
        col = 1
        row = row + 1

    workbook.save(filename=excelFile)

'''
    题目写入excel
'''
def writeQuestionsToXls(questionList,workSheetName):

    # 创建一个workbook 设置编码
    workbook = xlsxwriter.Workbook(excelFile)

    # 创建一个worksheet
    worksheet = workbook.add_worksheet(workSheetName)

    style = workbook.add_format({
        'bold': True,
        'align':'center',
        'valign':'vcenter',
        'font_size':14,
        'font_name':'Courier New',
    })

    # 写入excel
    # 参数对应 行, 列, 值
    worksheet.write(0, 0, 'WordID',style)
    worksheet.write(0, 1, 'Word',style)
    worksheet.write(0, 2, 'QuestionType',style)
    worksheet.write(0, 3, 'Subject',style)
    worksheet.write(0, 4, 'RightAnswer',style)
    worksheet.write(0, 5, 'ItemA',style)
    worksheet.write(0, 6, 'ItemB',style)
    worksheet.write(0, 7, 'ItemC',style)
    worksheet.write(0, 8, 'ItemD',style)

    contentStyle = workbook.add_format({
        'bold': False,
        'align': 'center',
        'valign': 'vcenter',
        'font_size': 14,
        'font_name': 'Courier New',
    })

    worksheet.set_column('A:C', 30,contentStyle)
    worksheet.set_column('D', 50,contentStyle)
    worksheet.set_column('E:I', 30,contentStyle)


    # 写入题目数据
    row = 1
    col = 0
    for question in questionList:
        worksheet.write(row,col,question.getWordID())
        worksheet.write(row,col+1,question.getWord())
        worksheet.write(row,col+2,question.getQuestionType())
        worksheet.write(row,col+3,question.getSubject())
        worksheet.write(row,col+4,question.getRightAnswer())
        worksheet.write(row,col+5,question.getItemA())
        worksheet.write(row,col+6,question.getItemB())
        worksheet.write(row,col+7,question.getItemC())
        worksheet.write(row,col+8,question.getItemD())
        col = 0
        row = row + 1

    # 关闭保存
    workbook.close()
    pass

'''
    链接sqlserver数据库
'''
def connsqlServer():
    connect = pymssql.connect('192.168.0.10', 'sa', '123456', 'Englisten')  # 服务器名,账户,密码,数据库名
    if connect:
        print("连接成功!")
    return connect

'''
    链接sqlite数据库
'''
def connsqlite():
    return db.connect(db_path)


if __name__ == "__main__":
    import pymssql
    nltk.download('wordnet')
    # wordList = selectSQLWord1(2130,2)
    # print(wordList[0].getWord()+"----"+wordList[0].getPart_Of_Speech())
    # word = re.sub("(.*?)", "", wordList[0].getWord())
    # print(reviserWord(word))
    # word = wordList[0].getWord()
    # print(word)
    # result = reviserWord(word)
    # print(result)

    # writeQuestionsToXls1([],"汉译英")
    # writeQuestionsToXls1([],"音译汉")
    # word = 'afterward(s)'
    #
    # isword = checkEnglishWords('afterward')
    # print(str(isword))
    # nlpCreateSentence('translate')
    # words = selectSQLWord('awake',2)
    # print(str(words[0].getID()))
    # print(reviserWord('cata(ue)log(11111)'))
    # print(str(trimStr('hair_(C)121     \nddd')))
    # collect = ['a','b','c','a','d','a','b']

    # for q in collect[:]:
    #     if q == 'a' or q == 'b':
    #         collect.remove(q)

    # for i in range(len(collect) - 1, -1, -1):
    #     if collect[i] == 'a' or collect[i] == 'b':
    #         del collect[i]
    #
    # print(collect)
    # aaa = 'I come from China!'
    # bbb = symbol_endwiths(aaa)
    # print(str(bbb))
    # aaa = "Is there a need to translate the psychiatrist's remarks?"
    # bbb = 'translate' in aaa
    # print(str(bbb))

    # raw = 'I have to translate when my in-laws from Austria visit the U.S.'
    # tokens = nltk.word_tokenize(raw)
    # default_tagger = nltk.DefaultTagger('NN')
    #
    # brown_tagged_sents = brown.tagged_sents(categories='news')
    # brown_sents = brown.sents(categories='news')
    # # 默认标注
    # tags = [tag for (word, tag) in brown.tagged_words(categories='news')]
    # print(nltk.FreqDist(tags).max())
    #
    # print(default_tagger.tag(tokens))
    # print(default_tagger.evaluate(brown_tagged_sents))
    # arr = ["kæb","ə'bændən","əd'væns","ə'mjuːzd","ə'mjuːzmənt"]
    # print(Levenshtein_ratio(arr,"ə'mjuːzd",0.2,5))

    # os.chmod('/Users/xuyan/nltk_data/stanford-postagger-full',777)
    # nlp = StanfordCoreNLP(r'/Users/xuyan/nltk_data/stanford-postagger-full/', lang='en')
    # sentence='Is there a need to translate the psychiatrists remarks?'
    #
    # print(nlp.word_tokenize(sentence))
    # print(nlp.pos_tag(sentence))
    # print(nlp.ner(sentence))
    # print(nlp.parse(sentence))
    # print(nlp.dependency_parse(sentence))
    # nlp.close()
    # example = "I have a pen!"
    # words = example.split(" ")
    # print(str(len(words)))
    # print(firstWordUpper("love"))
    # questionSentences = "______ I have a pen!"
    # index = toListIndex(questionSentences, "______")
    # print(str(index))